from PyQt5.QtWidgets import *
from PyQt5 import QtGui
import sys
from PyQt5 import QtWidgets, QtCore, QtGui
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
import pymysql
import base64
import io
import PIL.Image
import os
import pandas as pd
import interface.teacher.his_c as his_c
import pickle
import interface.teacher.sel_stu as sel_stu
import database.admin.setting as setting
class sel_stu_window(QMainWindow,sel_stu.Ui_Form):
    def __init__(self,tid,cid):
        QMainWindow.__init__(self)
        his_c.Ui_Form.__init__(self)
        self.tid=tid
        self.cid=cid
        self.setupUi(self)
        self.setWindowTitle("选课学生")
        self.quit.clicked.connect(self.close)
        self.excel.clicked.connect(self.convert_to_excel)
        self.res=None
        self.table.setColumnCount(5)
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table.horizontalHeader().setSectionResizeMode(0, QHeaderView.ResizeToContents)
        self.table.verticalHeader().setVisible(False)
        self.table.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table.verticalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        self.table.setHorizontalHeaderLabels(['学号', '姓名', '性别', '学院', '专业'])
        self.init_table()
    def init_table(self):
        with open("../admin/time.pkl", 'rb') as f:
            term = pickle.load(f)
        self.term = '2020-2021春学期'
        self.connect = pymysql.connect(**setting.config)
        self.cursor = self.connect.cursor()
        self.cursor.execute("select cname from course where id=%s",self.cid)
        self.name=self.cursor.fetchone()[0]
        print(self.name)
        sql_="select s.id,s.name,s.sex,s.school,s.major from students s ,select_course_state scs where scs.sid=s.id and scs.status=1 and scs.cid=%s and scs.time=%s"
        self.cursor.execute(sql_,(self.cid,self.term))
        result=self.cursor.fetchall()
        self.display_find_result(result)
    def display_find_result(self,re):
        self.table.setRowCount(0)
        row=self.table.rowCount()
        re_len=len(re)
        self.res=[]
        for l in range(re_len):
            #print(l)
            row = self.table.rowCount()
            self.table.setRowCount(row + 1)
            check=QtWidgets.QTableWidgetItem()
            check.setCheckState(QtCore.Qt.Unchecked)
            #self.table.setHorizontalHeaderLabels(['学号', '姓名', '性别', '学院', '专业'])
            #print(type(re))
            self.table.setItem(row,0,QTableWidgetItem(re[l][0]))
            self.table.setItem(row, 1, QTableWidgetItem(re[l][1]))
            self.table.setItem(row, 2, QTableWidgetItem(str(re[l][2])))
            self.table.setItem(row, 3, QTableWidgetItem(re[l][3]))
            self.table.setItem(row, 4, QTableWidgetItem(re[l][4]))
            self.res.append([re[l][0],re[l][1],re[l][2],re[l][3],re[l][4]])
    def convert_to_excel(self):#学号，姓名，性别，学院，专业
        print("Excel!")
        dir=QFileDialog.getExistingDirectory(self,"选择保存Excel的文件夹目录")
        print(dir)
        df = pd.DataFrame(self.res, columns=['学号', '姓名','性别', '学院', '专业'])
        trow=self.table.rowCount()
        df.to_excel(os.path.join(dir,"选修《"+self.name+"》课程的学生.xlsx"))
        QMessageBox.information(self,"提示","保存Excel文件成功！")
class his_c_window(QMainWindow,his_c.Ui_Form):###点击用户身份弹出的登陆小弹窗
    def __init__(self,id):
        QMainWindow.__init__(self)
        his_c.Ui_Form.__init__(self)
        self.id=id
        self.setupUi(self)
        self.setWindowTitle("已开设课程")
        self.box.addItem('ALL')
        self.find.clicked.connect(self.select)
        self.init_box()
        self.table.setColumnCount(6)
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.table.setHorizontalHeaderLabels(['课程号', '课程名','学分', '教材', '本学年选课人数','查看选课名单'])
        self.select()
        self.find.clicked.connect(self.select)
    def init_box(self):
        with open("../admin/time.pkl", 'rb') as f:
            term = pickle.load(f)
        self.term = '2020-2021春学期'
        self.connect = pymysql.connect(**setting.config, charset='utf8')
        self.cursor = self.connect.cursor()
        sql_ = "select cname from course where tid=%s"
        self.cursor.execute(sql_, self.id)
        re = self.cursor.fetchall()
        for i in range(len(re)):
            self.box.addItem(re[i][0])
        self.cursor.close()
        self.connect.close()
    def select(self):
        self.connect = pymysql.connect(**setting.config, charset='utf8')
        self.cursor = self.connect.cursor()
        cname=self.box.currentText()
        print(cname)
        if (cname== 'ALL'):
            sql_ = "select * from course where tid=%s"
            self.cursor.execute(sql_, self.id)
        else:
            sql_="select * from course where tid=%s and cname=%s"
            self.cursor.execute(sql_,(self.id,cname))
        re=self.cursor.fetchall()

        self.display_find_result(re)

        self.cursor.close()
        self.connect.close()
    def sel_but(self,cid,tid,cname):
        widget=QWidget()
        sel=QPushButton("查看")
        print(cid,tid)
        q=sel_stu_window(tid,cid)
        q.title.setText("选择"+"\""+cname+"\""+'课程的学生信息表')
        def load_():
            print("Yes")
            q.show()
        sel.clicked.connect(load_)

        return sel
    def display_find_result(self, re):
        self.table.setRowCount(0)
        row = self.table.rowCount()
        # self.table.setRowCount(row+1)
        re_len = len(re)
        for l in range(re_len):
            row = self.table.rowCount()
            self.table.setRowCount(row + 1)
            self.table.setItem(row, 0, QTableWidgetItem(re[l][0]))
            self.table.setItem(row, 1, QTableWidgetItem(re[l][1]))
            self.table.setItem(row, 2, QTableWidgetItem(str(re[l][2])))
            self.table.setItem(row, 3, QTableWidgetItem(re[l][5]))
            sql_="select stus from course_schedule where cid=%s and year_time=%s"
            print(self.term)
            self.cursor.execute(sql_,(re[l][0],self.term))
            re1=self.cursor.fetchone()
            self.table.setItem(row, 4, QTableWidgetItem(str(re1[0])+'人'))
            print(re1[0])
            self.table.setCellWidget(row, 5,(self.sel_but(re[l][0],self.id,re[l][1])))
            print(re1[0])
            break

if __name__=='__main__':
    app=QApplication(sys.argv)
    md=his_c_window('2018333333')
    md.show()
    sys.exit(app.exec_())
